#!/usr/bin/python3
# -*- coding: utf-8 -*-

import sqlite3
from datetime import datetime
from flask import current_app
from ..models.user import User
from ..utils.id_generator import IdGeneratorSingleton, UuidGenerator
from ..utils.db_utils import get_db_connection

class UserRepository:
    """用户数据访问仓储"""
    
    @staticmethod
    def get_db_connection():
        """获取数据库连接"""
        return sqlite3.connect(current_app.config['DB_PATH'])
    
    @classmethod
    def get_user_by_id(cls, user_id):
        """根据用户ID获取用户
        
        Args:
            user_id: 用户ID
            
        Returns:
            User: 用户对象或None
        """
        conn = cls.get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
        SELECT id, user_id, username, password, email, name, age, gender, address, phone, 
               birthday, company, job, website, token, created_time, updated_time
        FROM users 
        WHERE user_id = ?
        ''', (user_id,))
        
        row = cursor.fetchone()
        conn.close()
        
        return User.from_row(row) if row else None
    
    @classmethod
    def get_user_by_username(cls, username):
        """根据用户名获取用户
        
        Args:
            username: 用户名
            
        Returns:
            User: 用户对象或None
        """
        conn = cls.get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
        SELECT id, user_id, username, password, email, name, age, gender, address, phone, 
               birthday, company, job, website, token, created_time, updated_time
        FROM users 
        WHERE username = ?
        ''', (username,))
        
        row = cursor.fetchone()
        conn.close()
        
        return User.from_row(row) if row else None
    
    @classmethod
    def create_user(cls, user):
        """创建新用户
        
        Args:
            user: 用户对象或包含用户信息的字典
            
        Returns:
            User: 创建的用户对象，如果失败则返回None
        """
        if not isinstance(user, User):
            # 如果传入的是字典，转换为User对象
            user = User.from_dict(user)
        
        # 生成ID生成器实例
        id_generator = IdGeneratorSingleton.get_instance(current_app.config['WORKER_ID'])
        
        # 确保有用户ID (业务ID)
        if not user.user_id:
            user.user_id = id_generator.format_id()
            
        # 生成无短横线的UUID作为id
        if not user.id or isinstance(user.id, int):
            # 检查id_generator的实际类型，而不是检查_generator_type属性
            if isinstance(id_generator, UuidGenerator):
                # 如果使用UUID生成器，直接使用无短横线的UUID
                user.id = id_generator.format_id_no_hyphen()
            else:
                # 如果使用雪花算法，创建一个新的UUID生成器
                uuid_gen = UuidGenerator()
                user.id = uuid_gen.format_id_no_hyphen()
        
        # 设置创建时间和更新时间
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        user.created_time = current_time
        user.updated_time = current_time
        
        conn = cls.get_db_connection()
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
            INSERT INTO users (
                id, user_id, username, password, email, name, age, gender, address, phone,
                birthday, company, job, website, created_time, updated_time
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                user.id,
                user.user_id, 
                user.username, 
                user.password, 
                user.email, 
                user.name, 
                user.age, 
                user.gender, 
                user.address, 
                user.phone, 
                user.birthday, 
                user.company, 
                user.job, 
                user.website, 
                user.created_time, 
                user.updated_time
            ))
            
            conn.commit()
            return user
        except sqlite3.IntegrityError as e:
            # 违反唯一性约束等
            conn.rollback()
            print(f"创建用户失败 - 完整性错误: {str(e)}")
            raise ValueError(f"用户创建失败: {str(e)}")
        except Exception as e:
            conn.rollback()
            print(f"创建用户失败: {str(e)}")
            raise
        finally:
            conn.close()
    
    @classmethod
    def update_user(cls, user):
        """更新用户信息
        
        Args:
            user: 用户对象
            
        Returns:
            bool: 更新是否成功
        """
        if not isinstance(user, User):
            user = User.from_dict(user)
        
        # 首先获取现有用户数据
        existing_user = cls.get_user_by_id(user.user_id)
        if not existing_user:
            return False
            
        # 只更新非空字段，保留其他字段原有值
        update_fields = {}
        if user.username is not None:
            update_fields['username'] = user.username
        if user.password is not None:
            update_fields['password'] = user.password
        if user.email is not None:
            update_fields['email'] = user.email
        if user.name is not None:
            update_fields['name'] = user.name
        if user.age is not None:
            update_fields['age'] = user.age
        if user.gender is not None:
            update_fields['gender'] = user.gender
        if user.address is not None:
            update_fields['address'] = user.address
        if user.phone is not None:
            update_fields['phone'] = user.phone
        if user.birthday is not None:
            update_fields['birthday'] = user.birthday
        if user.company is not None:
            update_fields['company'] = user.company
        if user.job is not None:
            update_fields['job'] = user.job
        if user.website is not None:
            update_fields['website'] = user.website
        if user.token is not None:
            update_fields['token'] = user.token
            
        # 更新修改时间
        updated_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        update_fields['updated_time'] = updated_time
        
        # 如果没有要更新的字段，直接返回成功
        if not update_fields:
            return True
        
        conn = cls.get_db_connection()
        cursor = conn.cursor()
        
        try:
            # 构建动态SQL和参数
            set_clause = ', '.join([f"{field} = ?" for field in update_fields.keys()])
            sql = f"UPDATE users SET {set_clause} WHERE user_id = ?"
            
            # 准备参数
            params = list(update_fields.values())
            params.append(user.user_id)
            
            cursor.execute(sql, params)
            conn.commit()
            return cursor.rowcount > 0
        except Exception as e:
            conn.rollback()
            print(f"更新用户失败: {str(e)}")
            raise
        finally:
            conn.close()
    
    @classmethod
    def get_all_users(cls, limit=100, offset=0):
        """获取所有用户
        
        Args:
            limit: 限制返回数量
            offset: 偏移量
            
        Returns:
            list: 用户对象列表
        """
        conn = cls.get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
        SELECT id, user_id, username, password, email, name, age, gender, address, phone, 
               birthday, company, job, website, token, created_time, updated_time
        FROM users 
        LIMIT ? OFFSET ?
        ''', (limit, offset))
        
        rows = cursor.fetchall()
        conn.close()
        
        return [User.from_row(row) for row in rows]
    
    @classmethod
    def batch_create_users(cls, users):
        """批量创建用户
        
        Args:
            users: 用户对象列表
            
        Returns:
            tuple: (成功数量, 失败数量, 成功创建的用户列表)
        """
        success_count = 0
        error_count = 0
        successful_users = []
        
        # 获取现有的用户名、邮箱和用户ID集合，用于额外检查
        existing_usernames = set()
        existing_emails = set()
        existing_user_ids = set()
        
        # 使用连接池获取连接
        with get_db_connection() as conn:
            cursor = conn.cursor()
            
            try:
                # 获取所有现有用户名
                cursor.execute("SELECT username FROM users")
                for row in cursor.fetchall():
                    existing_usernames.add(row[0])
                
                # 获取所有现有邮箱
                cursor.execute("SELECT email FROM users WHERE email IS NOT NULL")
                for row in cursor.fetchall():
                    existing_emails.add(row[0])
                
                # 获取所有现有用户ID
                cursor.execute("SELECT user_id FROM users")
                for row in cursor.fetchall():
                    existing_user_ids.add(row[0])
            except Exception as e:
                print(f"获取现有用户信息失败: {str(e)}")
                # 继续执行，即使获取失败
        
        # 分批处理，每批50个
        batch_size = 50
        
        for i in range(0, len(users), batch_size):
            batch_users = users[i:i+batch_size]
            valid_batch_users = []
            
            # 预处理并验证当前批次的所有用户
            for user in batch_users:
                try:
                    if not isinstance(user, User):
                        user = User.from_dict(user)
                    
                    # 额外检查用户名和邮箱是否已存在
                    if user.username in existing_usernames:
                        error_count += 1
                        print(f"批量创建用户失败: 用户名已存在 - {user.username}")
                        continue
                    
                    if user.email and user.email in existing_emails:
                        error_count += 1
                        print(f"批量创建用户失败: 邮箱已存在 - {user.email}")
                        continue
                    
                    # 生成ID生成器实例
                    id_generator = IdGeneratorSingleton.get_instance()
                    
                    # 确保有用户ID (业务ID)，并确保唯一性
                    if not user.user_id:
                        # 使用完整的ID格式，不仅仅是前8位
                        user_id = None
                        max_tries = 10
                        tries = 0
                        
                        # 循环尝试生成唯一的user_id
                        while tries < max_tries:
                            if isinstance(id_generator, UuidGenerator):
                                user_id = f"USER-{id_generator.next_id()}"
                            else:
                                # 使用完整ID，不仅仅截取前8位
                                user_id = f"USER-{id_generator.next_id()}"
                            
                            if user_id not in existing_user_ids:
                                break
                            tries += 1
                        
                        if tries >= max_tries:
                            error_count += 1
                            print(f"批量创建用户失败: 无法生成唯一用户ID")
                            continue
                        
                        user.user_id = user_id
                        existing_user_ids.add(user_id)
                    elif user.user_id in existing_user_ids:
                        # 如果提供的user_id已存在，也要报错
                        error_count += 1
                        print(f"批量创建用户失败: 用户ID已存在 - {user.user_id}")
                        continue
                    else:
                        existing_user_ids.add(user.user_id)
                    
                    # 生成无短横线的UUID作为id
                    if not user.id or isinstance(user.id, int):
                        # 检查id_generator的实际类型，而不是检查_generator_type属性
                        if isinstance(id_generator, UuidGenerator):
                            # 如果使用UUID生成器，直接使用无短横线的UUID
                            user.id = id_generator.format_id_no_hyphen()
                        else:
                            # 如果使用雪花算法，创建一个新的UUID生成器
                            uuid_gen = UuidGenerator()
                            user.id = uuid_gen.format_id_no_hyphen()
                    
                    # 设置创建时间和更新时间
                    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    user.created_time = current_time
                    user.updated_time = current_time
                    
                    # 将有效的用户添加到批处理列表
                    valid_batch_users.append(user)
                    
                    # 将用户名和邮箱添加到已存在集合中，以避免同一批次中的重复
                    existing_usernames.add(user.username)
                    if user.email:
                        existing_emails.add(user.email)
                    
                except Exception as e:
                    error_count += 1
                    print(f"批量创建用户预处理失败: {str(e)}")
            
            # 如果有有效用户，一次性批量插入
            if valid_batch_users:
                with get_db_connection() as conn:
                    cursor = conn.cursor()
                    
                    try:
                        # 开始事务
                        conn.execute("BEGIN TRANSACTION")
                        
                        for user in valid_batch_users:
                            cursor.execute('''
                            INSERT INTO users (
                                id, user_id, username, password, email, name, age, gender, address, phone,
                                birthday, company, job, website, created_time, updated_time
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                            ''', (
                                user.id,
                                user.user_id, 
                                user.username, 
                                user.password, 
                                user.email, 
                                user.name, 
                                user.age, 
                                user.gender, 
                                user.address, 
                                user.phone, 
                                user.birthday, 
                                user.company, 
                                user.job, 
                                user.website, 
                                user.created_time, 
                                user.updated_time
                            ))
                        
                        # 提交整个批次
                        conn.execute("COMMIT")
                        
                        # 记录成功
                        success_count += len(valid_batch_users)
                        successful_users.extend(valid_batch_users)
                        
                    except Exception as e:
                        # 回滚事务
                        conn.execute("ROLLBACK")
                        print(f"批量创建用户事务失败: {str(e)}")
                        
                        # 如果整批失败，尝试逐个添加（回退机制）
                        for user in valid_batch_users:
                            try:
                                with get_db_connection() as single_conn:
                                    single_cursor = single_conn.cursor()
                                    single_cursor.execute('''
                                    INSERT INTO users (
                                        id, user_id, username, password, email, name, age, gender, address, phone,
                                        birthday, company, job, website, created_time, updated_time
                                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                                    ''', (
                                        user.id,
                                        user.user_id, 
                                        user.username, 
                                        user.password, 
                                        user.email, 
                                        user.name, 
                                        user.age, 
                                        user.gender, 
                                        user.address, 
                                        user.phone, 
                                        user.birthday, 
                                        user.company, 
                                        user.job, 
                                        user.website, 
                                        user.created_time, 
                                        user.updated_time
                                    ))
                                    success_count += 1
                                    successful_users.append(user)
                            except Exception as e2:
                                error_count += 1
                                print(f"批量创建用户单个插入失败: {str(e2)}")
        
        return success_count, error_count, successful_users 